package com.vertigrated.db.adventureworks;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import com.vertigrated.db.Database;
import com.vertigrated.db.SelectBuilder;
import com.vertigrated.text.StringUtil;

import javax.sql.DataSource;
import javax.sql.RowSet;
import java.sql.SQLException;

public class SelectPersonExample
{
    public static DataSource createDataSource()
    {
        final SQLServerDataSource ds = new SQLServerDataSource();
        ds.setServerName("localhost");
        ds.setDatabaseName("AdventureWorks");
        ds.setUser("aw"); // put your AdventureWorks login here
        ds.setPassword("adminaw"); // put your password here
        return ds;
    }

    public static void main(final String[] args)
    {
        final DataSource ds = createDataSource();
        final Database db = com.vertigrated.db.mssql.Database.getInstance(ds, "AdventureWorks");
        final SelectBuilder sb = db.createSelectBuilder("Person", "Contact");
        sb.addColumnName("ContactID");
        sb.addColumnName("FirstName");
        sb.addColumnName("LastName");
        sb.addWhereLike("LastName", "A%");
        sb.addOrderByAscending("LastName");
        sb.setMaxRows(10);
        final RowSet rs = db.executeSelect(sb);

        try
        {
            while (rs.next())
            {
                System.out.println(StringUtil.padLeftWithSpace(rs.getString("ContactID"), 4) + ":" + rs.getString("FirstName") + " " + rs.getString("LastName"));
            }
        }
        catch (SQLException e)
        {
            throw new RuntimeException(e);
        }

    }
}
